package com.shopx.core.utils;


import com.shopx.core.DTO.ExcelObjectDTO;
import com.shopx.core.common.CommonConstant;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * TODO
 *
 * @author:lituo
 * @date:2021/12/20
 **/
@Slf4j
public class ExcelUtil {

  /**
   * 创建单元格
   *
   * @param wb
   * @param row
   * @param column
   * @param value
   * @param cellStyle
   * @return void
   * @author lituo
   * @date 9:59 PM 2021/12/20
   */
  public static void createCell(Workbook wb, Row row, int column, String value, CellStyle cellStyle) {
    Cell cell = row.createCell(column);
    cell.setCellValue(value);
    cell.setCellStyle(cellStyle);
  }

  public static void createImgCell(Sheet sheet,Workbook wb, Row row, int column, String value, CellStyle cellStyle) {
    try {
      row.setHeight((short) 3000);
      sheet.setColumnWidth(column, column*1500);
      Cell cell = row.createCell(column);
      cell.setCellValue("");
      cell.setCellStyle(cellStyle);
      Drawing patriarch = sheet.createDrawingPatriarch();
      URL url = new URL("http://139.100.75.213:9002/oss/"+value.split(",")[0]);
      HttpURLConnection conn = (HttpURLConnection) url.openConnection();
      conn.setRequestMethod("GET");
      InputStream inStream = conn.getInputStream();
      byte[] byteData = readInputStream(inStream);
      HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) column, row.getRowNum(), (short) column, row.getRowNum());
      //插入图片
      patriarch.createPicture(anchor, wb.addPicture(byteData, HSSFWorkbook.PICTURE_TYPE_JPEG));
    }catch (Exception e){
      log.error("e:",e);
    }
  }

  public static byte[] readInputStream(InputStream inStream) throws Exception{
    ByteArrayOutputStream outStream = new ByteArrayOutputStream();
    //创建一个Buffer字符串
    byte[] buffer = new byte[1024];
    //每次读取的字符串长度，如果为-1，代表全部读取完毕
    int len = 0;
    //使用一个输入流从buffer里把数据读取出来
    while( (len=inStream.read(buffer)) != -1 ){
      //用输出流往buffer里写入数据，中间参数代表从哪个位置开始读，len代表读取的长度
      outStream.write(buffer, 0, len);
    }
    //关闭输入流
    inStream.close();
    //把outStream里的数据写入内存
    return outStream.toByteArray();
  }

  /**
   * 创建单元格
   *
   * @return void
   * @author lituo
   * @date 9:59 PM 2021/12/20
   * @param  wb
   * @param  row
   * @param  column
   * @param  value
   * @param  cellStyle
   */
  public static void createNumberCell(Workbook wb, Row row, int column, String value, CellStyle cellStyle) {
    Cell cell = row.createCell(column);
    if (StringUtil.isEmpty(value)) {
      cell.setCellValue("");
    } else {
      cell.setCellValue(Double.parseDouble(value));
    }
    cell.setCellStyle(cellStyle);
  }

  /**
   * 设置字体
   *
   * @param wb
   * @param bold
   * @param fontName
   * @param isItalic
   * @param hight
   * @return org.apache.poi.ss.usermodel.Font
   * @author lituo
   * @date 9:59 PM 2021/12/20
   */
  public static Font createFonts(Workbook wb, boolean bold, String fontName, boolean isItalic, short hight) {
    Font font = wb.createFont();
    font.setFontName(fontName);
    font.setBold(bold);
    font.setItalic(isItalic);
    font.setFontHeight(hight);
    return font;
  }

  /**
   * 设置标题
   *
   * @param wb
   * @return org.apache.poi.ss.usermodel.CellStyle
   * @author lituo
   * @date 9:58 PM 2021/12/20
   */
  public static CellStyle getTitleCellStyle(HSSFWorkbook wb) {
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    Font font = wb.createFont();
    font.setBold(true);
    cellStyle.setFont(font);
    cellStyle = borderFrame(cellStyle,false);
    return cellStyle;
  }


  /**
   * 设置副标题
   *
   * @param wb
   * @return org.apache.poi.ss.usermodel.CellStyle
   * @author lituo
   * @date 9:58 PM 2021/12/20
   */
  public static CellStyle getSubTitleCellStyle(HSSFWorkbook wb) {
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    Font font = wb.createFont();
    font.setBold(true);
    font.setFontHeightInPoints((short) 16);//设置字体大小
    cellStyle.setFont(font);
    cellStyle = borderFrame(cellStyle,true);
    return cellStyle;
  }

  /**
   * 设置普通单元格
   * @author lituo
   * @date 10:07 PM 2021/12/20
   * @param	wb
   * @return org.apache.poi.ss.usermodel.CellStyle
   */
  public static CellStyle getCmmCellStyle(HSSFWorkbook wb) {
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(HorizontalAlignment.LEFT);
    cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
    Font font = wb.createFont();
    font.setBold(false);
    cellStyle.setFont(font);
    cellStyle = borderFrame(cellStyle,true);
    return cellStyle;
  }

  public static CellStyle getNumberCellStyle(HSSFWorkbook wb) {
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(HorizontalAlignment.RIGHT);
    cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
    Font font = wb.createFont();
    font.setBold(false);
    cellStyle.setFont(font);
    cellStyle = borderFrame(cellStyle,true);
    return cellStyle;
  }

  public static CellStyle getImgCellStyle(HSSFWorkbook wb) {
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(HorizontalAlignment.LEFT);
    cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
    Font font = wb.createFont();
    font.setBold(false);
    cellStyle.setFont(font);
    cellStyle = borderFrame(cellStyle,true);
    return cellStyle;
  }

  /**
   * @descript 设置边框
   *
   * @param cellStyle
   * @param lineFlag
   * @author lituo
   * @date 2021/12/21 20:22
   * @return org.apache.poi.ss.usermodel.CellStyle
   */
  public static CellStyle borderFrame(CellStyle cellStyle,boolean lineFlag){
    cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
    cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
    cellStyle.setBorderTop(BorderStyle.THIN);// 上边框
    cellStyle.setBorderRight(BorderStyle.THIN);// 右边框
    cellStyle.setWrapText(lineFlag);// 设置主动换行
    return cellStyle;
  }


  /**
   * 写入excel文件
   * @author lituo
   * @date 10:17 PM 2021/12/20
   * @param	dto
   * @return java.lang.Object
   */
  public static HSSFWorkbook generatorExcel(ExcelObjectDTO dto) throws Exception{
    List<?> list = (List<?>)dto.getObj();
    // 列标题所在行
    int titleColumn = 0;
    // 内容所在行
    int ContentColumn = 0;
    HSSFWorkbook wb = new HSSFWorkbook();

    int pageSize = 15000;

    // 设置普通单元格
    CellStyle cellStrStyle = ExcelUtil.getCmmCellStyle(wb);
    // 设置普通单元格
    CellStyle cellNumberStyle = ExcelUtil.getNumberCellStyle(wb);
    // 设置图片单元格
    CellStyle cellImgStyle = ExcelUtil.getImgCellStyle(wb);
    // 设置普通单元格
    CellStyle cellTitleStyle = ExcelUtil.getTitleCellStyle(wb);
    // 设置普通单元格
    CellStyle cellSubTitleStyle = ExcelUtil.getTitleCellStyle(wb);
    //list下标，读取一行，+1
    int listIdx = 0;
    //5W条记录一个sheet
    final int sheetNum = (int) Math.ceil(dto.getSize() / pageSize)+1;
    for (int n = 1; n <= sheetNum; n++) {
      // 根据列表长度，计算sheet个数
      Sheet sheet = wb.createSheet("列表_" + n);
      if(!StringUtil.isEmpty(dto.getSubTitle())){
        //副标题不为空
        // 合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, dto.getTitle().length-1));
        // 创建第一行
        Row row = sheet.createRow(0);
        // 设置行高
        row.setHeight((short) 800);
        sheet.setColumnWidth((short) 0, (short) 250);
        //创建单元格
        ExcelUtil.createCell(wb, row, 0, dto.getSubTitle(), cellSubTitleStyle);
        titleColumn = 1;
        ContentColumn = 2;
      }else{
        titleColumn = 0;
        ContentColumn = 1;
      }

      // 创建标题
      Row row = sheet.createRow(titleColumn);
      row.setHeight((short) 500);
      sheet.setColumnWidth((short) 0, (short) 250);
      int i=0;
      for (String title:dto.getTitle()) {
        cellTitleStyle.setWrapText(true);
        ExcelUtil.createCell(wb, row, i, title.split(":")[1], cellTitleStyle);
        i++;
      }
      //循环写入内容
      for (int j=ContentColumn;j<=pageSize+ContentColumn;j++) {
        if( listIdx>=dto.getSize()){
          //list已经读取完成
          log.info("导出完成.listIdx:{}",listIdx);
          break;
        }
        Row rows = sheet.createRow(j);
        int k=0;
        for (String title:dto.getTitle()) {
          if (title.split(":").length == 2) {
            //如果标题被:分割(loginName:登录名)
            ExcelUtil.createCell(wb, rows, k,
                String.valueOf(ReflectUtils.getFieldValueByName(title.split(":")[0], list.get(listIdx))),
                cellStrStyle);
          }else if (title.split(":").length == 3) {
            if (CommonConstant.ExcelColType.NUMBER.equals(title.split(":")[2])) {
              //数字类型展示
              ExcelUtil.createNumberCell(wb, rows, k,
                  String.valueOf(ReflectUtils.getFieldValueByName(title.split(":")[0], list.get(listIdx))),
                  cellNumberStyle);
            } else if (CommonConstant.ExcelColType.STR.equals(title.split(":")[2])) {
              //普通类型展示
              ExcelUtil.createCell(wb, rows, k,
                  String.valueOf(ReflectUtils.getFieldValueByName(title.split(":")[0], list.get(listIdx))),
                  cellStrStyle);
            }  else if (CommonConstant.ExcelColType.IMG.equals(title.split(":")[2])) {
              //图片
              ExcelUtil.createImgCell(sheet,wb, rows, k,
                  String.valueOf(ReflectUtils.getFieldValueByName(title.split(":")[0], list.get(listIdx))),
                  cellImgStyle);
            }
          }else{
            //title不符合格式要求，此列不展示
          }
          k++;
        }
        listIdx++;
      }
      // 自动调整列宽度
      int j=0;
      for (String title:dto.getTitle()) {
        sheet.autoSizeColumn((short) j); // 调整指定宽度
        j++;
      }
    }
    return wb;
  }



  /**
   * @descript 读取excel数据，写入map
   *
   * @param filePath
   * @author lituo
   * @date 2021/12/21 18:24
   * @return java.util.List<java.util.Map < java.lang.String , java.lang.String>>
   */
  public static List<Map<String, Object>> readExcel(String filePath) {
    //定义工作簿
    InputStream inputStream = null;
    String sheetName = "";
    HSSFWorkbook hssfWorkbook = null;
    try {
      filePath = filePath.replaceAll("\\\\","/");
      URL url = new URL(filePath);
//      URL url = new URL("http://127.0.0.1:9001/oss/staticFile/20211221/99999/2503529898609042989.xlsx");
      inputStream = url.openStream();
      hssfWorkbook = new HSSFWorkbook(inputStream);
    } catch (Exception e) {
      throw new RuntimeException("导入失败.",e);
    }finally {
      try{
        if(inputStream!=null){
          inputStream.close();
        }
      }catch (Exception e){
        log.error("fis.close.error:",e);
      }
    }

    //定义工作表
    HSSFSheet hssfSheet;
    if (sheetName.equals("")) {
      // 默认取第一个子表
      hssfSheet = hssfWorkbook.getSheetAt(0);
    } else {
      hssfSheet = hssfWorkbook.getSheet(sheetName);
    }

    List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

    //定义行
    //默认第一行为标题行，index = 0
    HSSFRow titleRow = hssfSheet.getRow(0);

    //循环取每行的数据
    for (int rowIndex = 1; rowIndex < hssfSheet.getPhysicalNumberOfRows(); rowIndex++) {
      HSSFRow hssfRow = hssfSheet.getRow(rowIndex);
      if (hssfRow == null) {
        continue;
      }
      Map<String, Object> map = new LinkedHashMap<String, Object>();
      //循环取每个单元格(cell)的数据
      for (int cellIndex = 0; cellIndex < hssfRow.getLastCellNum(); cellIndex++) {
        HSSFCell titleCell = titleRow.getCell(cellIndex);
        HSSFCell xssfCell = hssfRow.getCell(cellIndex);
        map.put(getString(titleCell),getString(xssfCell));
      }
      list.add(map);
    }
    return list;
  }


  /**
   * 把单元格的内容转为字符串
   * @param hssfCell 单元格
   * @return 字符串
   */
  public static String getString(HSSFCell hssfCell) {
    if (hssfCell == null) {
      return "";
    }
    if (hssfCell.getCellTypeEnum() == CellType.NUMERIC) {
      return String.valueOf(hssfCell.getNumericCellValue());
    } else if (hssfCell.getCellTypeEnum() == CellType.BOOLEAN) {
      return String.valueOf(hssfCell.getBooleanCellValue());
    } else {
      return hssfCell.getStringCellValue();
    }
  }

  public static void main(String args[]) throws Exception{
    System.out.println((int) Math.ceil(10 / 3));
  }
}
